﻿CREATE PROCEDURE [dbo].[yaf_board_poststats](
                @BoardID INT)
AS
    BEGIN
        SELECT Posts = (SELECT COUNT(1)
                        FROM   yaf_Message a
                               JOIN yaf_Topic b
                                 ON b.TopicID = a.TopicID
                               JOIN yaf_Forum c
                                 ON c.ForumID = b.ForumID
                               JOIN yaf_Category d
                                 ON d.CategoryID = c.CategoryID
                        WHERE  d.BoardID = @BoardID  AND (a.Flags & 8)=0),
               Topics = (SELECT COUNT(1)
                         FROM   yaf_Topic a
                                JOIN yaf_Forum b
                                  ON b.ForumID = a.ForumID
                                JOIN yaf_Category c
                                  ON c.CategoryID = b.CategoryID
                         WHERE  c.BoardID = @BoardID AND (a.Flags & 8)=0),
               Forums = (SELECT COUNT(1)
                         FROM   yaf_Forum a
                                JOIN yaf_Category b
                                  ON b.CategoryID = a.CategoryID
                         WHERE  b.BoardID = @BoardID),
               Members = (SELECT COUNT(1)
                          FROM   yaf_User a
                          WHERE  a.BoardID = @BoardID),
               LastPostInfo.*,
               LastMemberInfo.*
        FROM   (SELECT   TOP 1 LastMemberInfoID = 1,
                               LastMemberID = UserID,
                               LastMember = Name
                FROM     yaf_User
                WHERE    dbo.yaf_bitset(Flags,2) = 1
                AND BoardID = @BoardID
                ORDER BY Joined DESC) AS LastMemberInfo
               LEFT JOIN (SELECT   TOP 1 LastPostInfoID = 1,
                                         LastPost = a.Posted,
                                         LastUserID = a.UserID,
                                         LastUser = e.Name
                          FROM     yaf_Message a
                                   JOIN yaf_Topic b
                                     ON b.TopicID = a.TopicID
                                   JOIN yaf_Forum c
                                     ON c.ForumID = b.ForumID
                                   JOIN yaf_Category d
                                     ON d.CategoryID = c.CategoryID
                                   JOIN yaf_User e
                                     ON e.UserID = a.UserID
                          WHERE    d.BoardID = @BoardID
                          ORDER BY a.Posted DESC) AS LastPostInfo
                 ON LastMemberInfoID = LastPostInfoID
    END


